﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

namespace MasApp.DAL
{
    public class reg
    {
        public reg()
        {
            //
            //TODO: 在此处添加构造函数逻辑
            //
        }
        public static int ExecuteNonQuery(string s)
        {
            SqlCommand cmd = MasApp.DbUtil.Db.conn();
            cmd.CommandText = s;
            int i = new int();
            try
            {
                cmd.Connection.Open();
                i = cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            catch (Exception ee) { }
            return i;
        }
        public static int InsertAppLevel(Model.applevel appl)
        {
            SqlCommand cmd = MasApp.DbUtil.Db.conn();
            string str;
            str = "INSERT INTO applevel(levelmissionid, indicatorid, levelid) "
                + "VALUES        ("
                + appl.Levelmissionid
                + ","
                + appl.Indicatorid
                + ","
                + appl.Levelid
                + ")";
            cmd.CommandText = str;
            int i = 0;
            appl.Applevelid = 0;
            try
            {
                cmd.Connection.Open();
                i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    str = "SELECT applevelid FROM applevel ORDER BY applevelid DESC";
                    cmd.CommandText = str;
                    appl.Applevelid = int.Parse(cmd.ExecuteScalar().ToString());
                }
                cmd.Connection.Close();
            }
            catch (Exception ee)
            {
            }
            return appl.Applevelid;
        }
//以下使用存储过程
        /// <summary>
        /// 插入等级
        /// </summary>
        /// <param name="lev"></param>
        /// <returns></returns>
        public static int InsertLevel(MasApp.Model.level lev)
        {
            SqlCommand cmd = MasApp.DbUtil.Db.conn();
            cmd.CommandText = "AddLevel";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@levelname", SqlDbType.VarChar, 50).Value = lev.Levelname;
            cmd.Parameters.Add("@judgement", SqlDbType.VarChar, 50).Value = lev.Judgement;
            cmd.Parameters.Add("@methodid", SqlDbType.Int).Value = lev.Methodid;
            cmd.Parameters.Add("@levelvalue", SqlDbType.Float).Value = lev.Levelvalue;
            int i = 0;
            try
            {
                cmd.Connection.Open();
                i = cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            catch (Exception ee)
            {
            }
            return i;
        }
        /// <summary>
        /// 插入模型
        /// </summary>
        /// <param name="mod"></param>
        /// <returns></returns>
        public static int InsertModel(MasApp.Model.model mod)
        {
            SqlCommand cmd = MasApp.DbUtil.Db.conn();
            cmd.CommandText = "AddModel";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@modelname", SqlDbType.VarChar, 50).Value = mod.Modelname;
            cmd.Parameters.Add("@creator", SqlDbType.VarChar, 50).Value = mod.Creator;
            cmd.Parameters.Add("@target", SqlDbType.VarChar, 50).Value = mod.Target;
            cmd.Parameters.Add("@description", SqlDbType.VarChar, 100).Value = mod.Description;
            cmd.Parameters.Add("@organizationid", SqlDbType.Int).Value = mod.Organizationid;
            int i = 0;
            try
            {
                cmd.Connection.Open();
                i = cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            catch (Exception ee)
            { }
            return i;
        }
        /// <summary>
        /// 插入方法
        /// </summary>
        /// <param name="method"></param>
        /// <returns></returns>
        public static int InsertMethod(Model.method method)
        {
            SqlCommand cmd = DbUtil.Db.conn();
            cmd.CommandText = "AddMethod";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@methodname", SqlDbType.VarChar, 50).Value = method.Methodname;
            cmd.Parameters.Add("@creator", SqlDbType.VarChar, 50).Value = method.Creator;
            cmd.Parameters.Add("@description", SqlDbType.VarChar, 100).Value = method.Description;
            cmd.Parameters.Add("@organizationid", SqlDbType.Int).Value = method.Organizationid;
            int i = 0;
            try
            {
                cmd.Connection.Open();
                i = cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            catch (Exception ee)
            {
            }
            return i;
        }
        /// <summary>
        /// 插入准则
        /// </summary>
        /// <param name="rule"></param>
        /// <returns></returns>
        public static int InsertRule(Model.rule rule)
        {

            SqlCommand cmd = DbUtil.Db.conn();
            cmd.CommandText = "AddRule";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@rulename", SqlDbType.VarChar, 50).Value = rule.Rulename;
            cmd.Parameters.Add("@description", SqlDbType.VarChar, 100).Value = rule.Description;
            cmd.Parameters.Add("@modelid", SqlDbType.Int).Value = rule.Modelid;
            int i = 0;
            try
            {
                cmd.Connection.Open();
                i = cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            catch (Exception ee)
            {
            }
            return i;
        }

//以下返回 id 值
        /// <summary>
        /// 插入指标，返回 indicatorid
        /// </summary>
        /// <param name="indi"></param>
        /// <returns></returns>
        public static int InsertIndicator(Model.indicator indi)
        {
            SqlCommand cmd = MasApp.DbUtil.Db.conn();
            string str;
            str = "insert into indicator(indicatorname,ruleid,description) values('";
            str += indi.Indicatorname + "',";
            str += indi.Ruleid + ",'";
            str += indi.Description + "')";
            cmd.CommandText = str;
            int i = 0;
            indi.Indicatorid = 0;
            try
            {
                cmd.Connection.Open();
                i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    str = "SELECT indicatorid FROM indicator ORDER BY indicatorid DESC";
                    cmd.CommandText = str;
                    indi.Indicatorid = int.Parse(cmd.ExecuteScalar().ToString());
                }
                cmd.Connection.Close();
            }
            catch (Exception ee)
            {
                //System.Web.HttpContext.Current.Response.Redirect("~/showerror.aspx");
            }
            return indi.Indicatorid;
        }
        
        /// <summary>
        /// 插入项目，返回 projectid
        /// </summary>
        /// <param name="pro"></param>
        /// <returns></returns>
        public static int InsertProject(Model.project pro)
        {
            SqlCommand cmd = MasApp.DbUtil.Db.conn();
            string str;
            str = "insert into project(projectname,creator,modelid,methodid,starttime,endtime,description,organizationid, weighttype) "
                + " values('";
            str += pro.Projectname + "','";
            str += pro.Creator + "',";
            str += pro.Modelid + ",";
            str += pro.Methodid + ",'"
            +pro.Starttime+"','"+pro.Endtime+"','";
            str += pro.Description + "',";
            str += pro.Organizationid + ",'";
            str += pro.Weighttype + "')";
            cmd.CommandText = str;
            
            int i = 0;
            try
            {
                cmd.Connection.Open();
                i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    str = "SELECT projectid FROM project ORDER BY projectid DESC";
                    cmd.CommandText = str;
                    i = int.Parse(cmd.ExecuteScalar().ToString());
                }
                pro.Projectid = i;
                cmd.Connection.Close();
            }
            catch (Exception ee)
            {
                //System.Web.HttpContext.Current.Response.Redirect("~/showerror.aspx");
            }
            return pro.Projectid;
        }

        /// <summary>
        /// 插入用户角色，返回 userroleid
        /// </summary>
        /// <param name="ur"></param>
        /// <returns></returns>
        public static int InsertUserRole(MasApp.Model.userrole ur)
        {
            SqlCommand cmd = MasApp.DbUtil.Db.conn();
            string str = "insert into userrole(userid,roleid) values(" + ur.Userid + "," + ur.Roleid + ")";
            cmd.CommandText = str;
            int i = 0;
            ur.Userroleid = 0;
            try
            {
                cmd.Connection.Open();
                i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    str = "SELECT userroleid FROM userrole ORDER BY userroleid DESC";
                    cmd.CommandText = str;
                    ur.Userroleid = int.Parse(cmd.ExecuteScalar().ToString());
                }
                cmd.Connection.Close();
            }
            catch (Exception ee)
            {

                //System.Web.HttpContext.Current.Response.Redirect("~/showerror.aspx");
            }
            return ur.Userroleid;
        }

        /// <summary>
        /// 插入用户信息，返回userid
        /// </summary>
        /// <param name="users"></param>
        /// <returns></returns>
        public static int InsertUsers(MasApp.Model.users users)
        {
            string str;
            SqlCommand cmd = MasApp.DbUtil.Db.conn();
            str = "insert into users(username,realname,sex,email,mphone,phone,address,postcode,"
                + "organizationid,description) values('";
            str += users.Username + "','";
            str += users.Realname + "','";
            str += users.Sex + "','";
            str += users.Email + "','";
            str += users.Mphone + "','";
            str += users.Phone + "','";
            str += users.Address + "','";
            str += users.Postcode + "',";
            str += users.Organizationid + ",'";
            str += users.Description + "')";
            cmd.CommandText = str;
            int i = 0;
            users.Userid = 0;
            try
            {
                cmd.Connection.Open();
                i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    str = "SELECT userid FROM users ORDER BY userid DESC";
                    cmd.CommandText = str;
                    users.Userid = int.Parse(cmd.ExecuteScalar().ToString());
                }
                cmd.Connection.Close();
            }
            catch (Exception ee)
            {
                /*
                                System.Web.HttpContext.Current.Response.Redirect("~/showerror.aspx");*/
            }
            return users.Userid;
        }

        /// <summary>
        /// 插入组织信息，返回organizationid
        /// </summary>
        /// <param name="org"></param>
        /// <returns></returns>
        public static int InsertOrg(MasApp.Model.organization org)
        {
            SqlCommand cmd = MasApp.DbUtil.Db.conn();
            string str;
            str = "insert into organization(creator,organizationname,address,postcode,phone,contactperson,"
                + "contactemail,description) values('";
            str += org.Creator + "','";
            str += org.Organizationname + "','";
            str += org.Address + "','";
            str += org.Postcode + "','";
            str += org.Phone + "','";
            str += org.Contactperson + "','";
            str += org.Contactemail + "','";
            str += org.Description + "')";
            cmd.CommandText = str;
            int i = 0;
            org.Organizationid = 0;
            try
            {
                cmd.Connection.Open();
                i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    str = "SELECT organizationid FROM organization ORDER BY organizationid DESC";
                    cmd.CommandText = str;
                    org.Organizationid = int.Parse(cmd.ExecuteScalar().ToString());
                }
                cmd.Connection.Close();
            }
            catch (Exception ee)
            {
                //System.Web.HttpContext.Current.Response.Redirect("~/showerror.aspx");
            }
            return org.Organizationid;
        }

        /// <summary>
        /// 插入权重任务返回 weightmissionid
        /// </summary>
        /// <param name="wm"></param>
        /// <returns></returns>
        public static int InsertWeightMission(Model.weightmission wm)
        {
            SqlCommand cmd = MasApp.DbUtil.Db.conn();
            string str;
            str = "insert into weightmission(weightmissionname,weightmissiontype,"
                + "actor, projectid, state, description) "
                + " values('";
            str += wm.Weightmissionname + "', '";
            str += wm.Weightmissiontype + "', '";
            str += wm.Actor + "', ";
            str += wm.Projectid + ", '";
            str += wm.State + "', '";
            str += wm.Description + "')";
            cmd.CommandText = str;
            int i = 0;
            wm.Weightmissionid = 0;
            try
            {
                cmd.Connection.Open();
                i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    str = "SELECT weightmissionid FROM weightmission ORDER BY weightmissionid DESC";
                    cmd.CommandText = str;
                    wm.Weightmissionid = int.Parse(cmd.ExecuteScalar().ToString());
                }
                cmd.Connection.Close();
            }
            catch (Exception ee)
            {
                //System.Web.HttpContext.Current.Response.Redirect("~/showerror.aspx");
            }
            return wm.Weightmissionid;
        }

        /// <summary>
        /// 插入权重，返回 weightid
        /// </summary>
        /// <param name="we"></param>
        /// <returns></returns>
        public static int InsertWeight(Model.weight we)
        {
            SqlCommand cmd = MasApp.DbUtil.Db.conn();
            string str;
            str = "INSERT INTO weight(elementid,elementtype, weightvalue, weightmissionid) "
                + " VALUES(";
            str += we.Elementid + ",'";
            str += we.Elementtype + "',";
            str += we.Weightvalue + ",";
            str += we.Weightmissionid + ")";
            cmd.CommandText = str;
            int i = 0;
            we.Weightid = 0;
            try
            {
                cmd.Connection.Open();
                i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    str = "SELECT weightid FROM weight ORDER BY weightid DESC";
                    cmd.CommandText = str;
                    we.Weightid = int.Parse(cmd.ExecuteScalar().ToString());
                }
                cmd.Connection.Close();
            }
            catch (Exception ee)
            {
                //System.Web.HttpContext.Current.Response.Redirect("~/showerror.aspx");
            }
            return we.Weightid;
        }

        /// <summary>
        /// 插入等级任务，返回 levelmissionid
        /// </summary>
        /// <param name="lm"></param>
        /// <returns></returns>
        public static int InsertLevelMission(Model.levelmission lm)
        {
            SqlCommand cmd = MasApp.DbUtil.Db.conn();
            string str;
            str = "insert into levelmission("
                + "actor, actorroleid, projectid, type, state) "
                + " values('";
            str += lm.Actor + "', ";
            str += lm.Actorroleid + " , ";
            str += lm.Projectid + ", '";
            str += lm.Type + "', '";
            str += lm.State + "')";
            cmd.CommandText = str;
            int i = 0;
            lm.Levelmissionid = 0;
            try
            {
                cmd.Connection.Open();
                i = cmd.ExecuteNonQuery();
                if (i > 0)
                {
                    str = "SELECT levelmissionid FROM levelmission ORDER BY levelmissionid DESC";
                    cmd.CommandText = str;
                    lm.Levelmissionid = int.Parse(cmd.ExecuteScalar().ToString());
                }
                cmd.Connection.Close();
            }
            catch (Exception ee)
            {
                //System.Web.HttpContext.Current.Response.Redirect("~/showerror.aspx");
            }
            return lm.Levelmissionid;
        }


        /// <summary>
        /// 插入用户登录信息，返回影响行数
        /// </summary>
        /// <param name="log"></param>
        /// <returns></returns>
        public static int InsertLogin(MasApp.Model.login log)
        {
            SqlCommand cmd = MasApp.DbUtil.Db.conn();
            string str;
            str = "insert into login(username,password) values('";
            str += log.Username + "','";
            str += log.Password + "')";
            cmd.CommandText = str;
            int i = 0;
            try
            {
                cmd.Connection.Open();
                i = cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            catch (Exception ee)
            {

            }
            return i;
        }
    }
}